<?php
/////////////////////////////////////////////////////////
//Tao mang du lieu xuat ra excel

error_reporting(E_ALL & ~E_NOTICE);
session_start();
include_once("includes/globalvars.php");
//include_once("includes/functions.php");
include_once("excel/exce/Writer.php");
//include_once("excel/exce/IssueList.php");
if(!isset($_SESSION['lang'])) $_SESSION['lang'] = "vi";
if(!ConnectDB())
	die("");
putenv("TZ=Asia/Bangkok");	
$group = "_";
	$group .= isset($_SESSION['datagroup']) ? $_SESSION['datagroup'] : (isset($_SESSION['datagroup2']) ? $_SESSION['datagroup2'] : "");
	 if($group == "_")
		$group = "";
$powerid = isset($_SESSION['power_DL']) ? (int)$_SESSION['power_DL'] : 0;
$user = isset($_SESSION['user_DL']) ? trim($_SESSION['user_DL']) : "";

$nDevID = isset($_POST['ddlDevice']) ? (int)$_POST['ddlDevice'] : (isset($_GET['did']) ? (int)$_GET['did'] : 0);
$nMONTH1 = isset($_POST['txtFromDate']) ? trim($_POST['txtFromDate']) : (isset($_GET['fd']) ? trim($_GET['fd']) : Date("d/m/Y"));	
$nMONTH2 = isset($_POST['txtToDate']) ? trim($_POST['txtToDate']) : (isset($_GET['td']) ? trim($_GET['td']) : Date("d/m/Y"));	
$nTIME1 = isset($_POST['txtFromTime']) ? trim($_POST['txtFromTime']) : (isset($_GET['ft']) ? trim($_GET['ft']) : Date("00:00"));	
$nTIME2 = isset($_POST['txtToTime']) ? trim($_POST['txtToTime']) : (isset($_GET['tt']) ? trim($_GET['tt']) : Date("23:59"));
if(strlen($nMONTH1) != 10) $nMONTH1 = Date("d/m/Y");
if(strlen($nMONTH2) != 10) $nMONTH2 = Date("d/m/Y");
if(strlen($nTIME1) != 5) $nTIME1 = Date("00:00");
if(strlen($nTIME2) != 5) $nTIME2 = Date("23:59");	

$sFromDate = ToYMD($nMONTH1);
$sToDate = ToYMD($nMONTH2);	

$arrDisplay = Array();
$arrDisplay2 = Array();
$arrtemp=Array();
$fplace = Array();
$mRow = 0;
$sSQL = "SELECT devstatus,distance,trktime,address, 
					(SELECT mapname FROM tblmaps WHERE mapid = d.mapid) AS mapname
			 FROM tbldata$group d
			 WHERE deviceid = $nDevID AND nogps = 0 and devstatus != 2
				AND trktime > '$sFromDate $nTIME1:00' AND  trktime  < '$sToDate $nTIME2:59' ORDER BY trktime ASC";
	
	$rs = $dbConnect->Execute($sSQL);
	//die($sSQL);
	if($rs)
	{
		$curMap = 0;
		$curName = $_SESSION['lang'] == "en" ? "Unnamed" : "Chưa xác định";
		$curTime = $_SESSION['lang'] == "en" ? "Undefined" : "Không xác định";
		$Distance = 0;
		$Stoptime = 0;
		$Movetime = 0;
		$AvgSpeed = 0;
		$i = 0;
		while($r = $rs->FetchRow())
		{
			
			if($i == 0)
			{
				//$Name = isset($r['mapname']) ? $r['mapname'] : (isset($r['address']) ? $r['address'] : "");		
				$Name = isset($r['mapname']) ? trim($r['mapname']) : (isset($r['address']) ? trim($r['address']) : "");
				if($Name =='')
					$Name  = ($_SESSION['lang'] == "en" ? "Unnamed" : "Chưa xác định");
				$fplace[$i] = $Name;				
				$curstatus = isset($r['devstatus']) ? (int)$r['devstatus'] : 0;
				$Distance = isset($r['distance']) ? (float)$r['distance'] : 0;
				//$Stoptime = isset($r['stoptime']) ? (float)$r['stoptime'] : 0;
				//$Movetime = isset($r['movetime']) ? (float)$r['movetime'] : 0;
				$curTime = isset($r['trktime']) ? Date("d/m/Y H:i:s", strtotime(trim($r['trktime']))) : ($_SESSION['lang'] == "en" ? "Undefined" : "Không xác định");
				$curTime1 = isset($r['trktime']) ? $r['trktime'] : "";
				if($rs->RecordCount() == 1)
				{
					//$Name = isset($r['mapname']) ? trim($r['mapname']) : ($_SESSION['lang'] == "en" ? "Unnamed" : "Chưa xác định");
					array_push($arrDisplay, Array(	"status"=>$status,
													"from"=>$curTime, 
													"to"=>$curTime, //
													"tttime"=>"00:00",
													"frplace"=>$Name,
													"toplace"=>$Name,
													"distance"=>0
												));
					$mRow++;
				}
			}
			elseif($i + 1 == $rs->RecordCount())
			{
				$Name = isset($r['mapname']) ? trim($r['mapname']) : (isset($r['address']) ? trim($r['address']) : "");
				if($Name =='')
					$Name  = ($_SESSION['lang'] == "en" ? "Unnamed" : "Chưa xác định");
				//Tinh van toc trung binh
				array_push($arrDisplay, Array(	"status"=>($Distance > 0 ? 1 : 0 ),
												"from"=>$curTime,
												"to"=>isset($r['trktime']) ? Date("d/m/Y H:i:s", strtotime(trim($r['trktime']))) : ($_SESSION['lang'] ? "Undefined" : "Không xác dịnh"), 
												"tttime"=>(strtotime($r['trktime']) - strtotime($curTime1) ),
												"frplace"=>$Name,
												"toplace"=>$Name,
												"distance"=>$Distance 

											));
				$mRow++;
			}
			else
			{
				$status = isset($r['devstatus']) ? (int)$r['devstatus'] : 0;
				//$Name = isset($r['mapname']) ? trim($r['mapname']) : ($_SESSION['lang'] == "en" ? "Unnamed" : "Chưa xác định");
				
				if($curstatus != $status)
				{
					$prevMove = isset($prevRow['movetime']) ? (int)$prevRow['movetime'] : 0;
					$prevDist = isset($prevRow['distance']) ? (float)$prevRow['distance'] : 0;
					$tttime =(strtotime($prevRow['trktime']) - strtotime($curTime1) );
					$stoplace = isset($prevRow['mapname']) ? trim($prevRow['mapname']) : (isset($prevRow['address']) ? trim($prevRow['address']) : "");
						if($stoplace =='')
							$stoplace  = ($_SESSION['lang'] == "en" ? "Unnamed" : "Chưa xác định");
						if($tttime >= 600 && $curstatus ==1 || $curstatus == 0 )
							{
								if($mRow == 0)
									{								
										$Name =$fplace[0];
									}
									array_push($arrDisplay, Array(
																	"status"=>$status,
																	"from"=>$curTime,
																	"to"=>isset($prevRow['trktime']) ? Date("d/m/Y H:i:s", strtotime(trim($prevRow['trktime']))) : ($_SESSION['lang'] ? "Undefined" : "Không xác dịnh"),  
																	"tttime"=>(strtotime($prevRow['trktime']) - strtotime($curTime1) ),
																	"frplace"=> $Name,
																	"toplace"=>$stoplace ,
																	//"distance"=>abs($Distance - $prevDist)
																	"distance"=>abs($Distance)
																	
																));
									$curstatus = $status;
									$curTime = isset($prevRow['trktime']) ? Date("d/m/Y H:i:s", strtotime(trim($prevRow['trktime']))) : ($_SESSION['lang'] == "en" ? "Undefined" : "Không xác định");
									$curTime1 = isset($prevRow['trktime']) ? $prevRow['trktime'] : "";
									//$curTime = isset($r['trktime']) ? Date("d/m/Y H:i:s", strtotime(trim($r['trktime']))) : ($_SESSION['lang'] == "en" ? "Undefined" : "Không xác định");
									$mRow++;
									$Name = isset($prevRow['mapname']) ? $prevRow['mapname'] : (isset($prevRow['address']) ? $prevRow['address'] : ($_SESSION['lang'] == "en" ? "Unnamed" : "Chưa xác định"));				
										if($Name =='')
											$Name  = ($_SESSION['lang'] == "en" ? "Unnamed" : "Chưa xác định");
									//$Distance = isset($r['distance']) ? (float)$r['distance'] : 0;
									$Distance = 0;
									$Stoptime = isset($r['stoptime']) ? (float)$r['stoptime'] : 0;
									$Movetime = isset($r['movetime']) ? (float)$r['movetime'] : 0;
									if($prevMove > 0) $Movetime = $prevMove;
							}
						else
							{
								$curstatus = $status;
							}
						
				}
				else
				{
					//if($status == 1)
						$Distance += isset($r['distance']) ? (float)$r['distance'] : 0;
				}
			}
			$prevRow = $r;
			//echo $Map."   ".$curMap;
			$i++;
		}
	}
	else
	{
		MsgBox1("Bi loi");
	}	

	//msgBox1($mRow);
	//ListDataArray($mRow, $arrDisplay, "blk_list");// OK roi
	for($j=0;$j<$mRow;$j++)
		{
			//$tam = $arrDisplay[$j]['distance'];
			if($arrDisplay[$j]['status'] == 0)
				{
					//MsgBox1($j." day ". $fromtime);			
					array_push($arrDisplay2, Array("status"=>($_SESSION['lang'] == "en" ? "Stop" : "Dừng"),
														  "from"=>$arrDisplay[$j]['from'] , 
														  "to"=>$arrDisplay[$j]['to'], 
														  "frplace"=>$arrDisplay[$j]['frplace'],
														  "toplace"=>$arrDisplay[$j]['frplace'],
														  "distance"=>$arrDisplay[$j]['distance'],	
														  //"tttime"=>TimeToHM($arrDisplay[$j]['tttime']/60)
														  "tttime"=>TimeToHM((strtotime(changetime($arrDisplay[$j]['to'])) - strtotime(changetime($arrDisplay[$j]['from'])))/60)
														  ));
					/*array_push($arrtemp,Array(($_SESSION['lang'] == "en" ? "Stop" : "Dừng"),$arrDisplay[$j]['from'],$arrDisplay[$j]['to'],
										$arrDisplay[$j]['frplace'],$arrDisplay[$j]['frplace'],$arrDisplay[$j]['distance'],
										TimeToHM((strtotime(changetime($arrDisplay[$j]['to'])) - strtotime(changetime($arrDisplay[$j]['from'])))/60))
										);*/					  
							$dem++;
							//MsgBox1($arrDisplay[$mRow]['status']);
				}
			else
				{	
					//$first = $arrDisplay[$j]['from'] ;
					$fromtime = $arrDisplay[$review]['from'];
					$fromplace = $arrDisplay[$review]['frplace'];
					if($arrDisplay[$j+1]['status'] == 1)					
						{	
							$distance += $arrDisplay[$j]['distance'];
							$tttime += $arrDisplay[$j]['tttime'];
						}
					
					if($dem == 0)
						{
							$fromtime = $arrDisplay[0]['from'];
							$fromplace = $arrDisplay[0]['frplace'];
						}
							
					if($arrDisplay[$j+1]['status'] == 0 && $arrDisplay[$j-1]['status']==1 )
						{
							//MsgBox1($tttime);
							//MsgBox1($j." tren ". $fromtime);			
							array_push($arrDisplay2, Array("status"=>($_SESSION['lang'] == "en" ? "Move" : "Chạy"),
														  "from"=>$fromtime, 
														  "to"=>$arrDisplay[$j]['to'], 
														  "frplace"=>$fromplace,
														  "toplace"=>$arrDisplay[$j]['toplace'],
														  "distance"=>$distance + $arrDisplay[$j]['distance'],
														  "tttime"=>TimeToHM((strtotime(changetime($arrDisplay[$j]['to'])) - strtotime(changetime($fromtime)))/60),
														  "stoptime"=>"00:00",
														  "movetime"=>"00:00"));
							/*array_push($arrtemp,Array(($_SESSION['lang'] == "en" ? "Move" : "Chạy"),$fromtime,$arrDisplay[$j]['to'],
													$fromplace,$arrDisplay[$j]['frplace'],$arrDisplay[$j]['toplace'],$distance + $arrDisplay[$j]['distance'],
													TimeToHM((strtotime(changetime($arrDisplay[$j]['to'])) - strtotime(changetime($arrDisplay[$j]['from'])))/60),
													"00:00","00:00"
													));	*/							  
							$distance = 0;	
							$tttime = 0;							
							$dem++;
						}
					if($arrDisplay[$j+1]['status'] == 0 && $arrDisplay[$j-1]['status']==0 )
					{
						//MsgBox1($j." duoi ". $fromtime);
						array_push($arrDisplay2, Array("status"=>($_SESSION['lang'] == "en" ? "Move" : "Chạy"),
													  "from"=>$arrDisplay[$j]['from'], 
													  "to"=>$arrDisplay[$j]['to'], 
													  "frplace"=>$arrDisplay[$j]['frplace'],
													  "toplace"=>$arrDisplay[$j]['toplace'],
													  "distance"=>$arrDisplay[$j]['distance'],
													  "tttime"=>TimeToHM((strtotime(changetime($arrDisplay[$j]['to'])) - strtotime(changetime($arrDisplay[$j]['from'])))/60)
													  ));
						/*array_push($arrtemp,Array(($_SESSION['lang'] == "en" ? "Move" : "Chạy"),$arrDisplay[$j]['from'],$arrDisplay[$j]['to'],
													$arrDisplay[$j]['frplace'],$arrDisplay[$j]['frplace'],$arrDisplay[$j]['toplace'],$arrDisplay[$j]['distance'],
													TimeToHM((strtotime(changetime($arrDisplay[$j]['to'])) - strtotime(changetime($arrDisplay[$j]['from'])))/60))
									);	*/						  
						$distance = 0;	
						$tttime = 0;							
						$dem++;
						
					}
					
					
				}
				$review = $j;
				
		}
		/*$data = array(
			array('Tên', 'Tuổi', 'Quê', 'Lưu ý'),
			array('John', 24, 54, 38),
			array('Mark', 67, 22, 57),
			array('Tim', 69, 32, 58),
			array('Hán Văn Thắng', 81, 78, 68),
			array('Hoàng Phan Bảo Trung', 16, 44, 38),
			array('Nguyễn Tất Đắc', 16, 44, 38),
		);*/
		//print_r($data);
		//print_r($arrtemp);
		//Msgbox1($arrtemp[0][0]);
		//die();
		/*$test = new IssueList($arrtemp);


		$test->setFileName("excel_dat.xls");

		$test->setSheetName("MySheEt");

		if ($test->save()){
			$test->download();
		}else
		{
			//echo "BUG";
		}*/
		$workbook = new Spreadsheet_Excel_Writer();
		$workbook->setVersion(8, 'utf-8');
		$format_bold =& $workbook->addFormat();
		$format_bold->setBold();
		$worksheet =& $workbook->addWorksheet();
		$worksheet->setInputEncoding('utf-8');
		$worksheet->write(0, 0, "STT", $format_bold);
		$worksheet->write(0, 1, "Trạng thái", $format_bold);
		$worksheet->write(0, 2, "Thời gian đầu", $format_bold);
		$worksheet->write(0, 3, "Thời gian cuối", $format_bold);
		$worksheet->write(0, 4, "Tổng TG", $format_bold);
		$worksheet->write(0, 5, "Vị trí đầu", $format_bold);
		$worksheet->write(0, 6, "Vị trí cuối", $format_bold);
		$worksheet->write(0, 7, "Tổng km", $format_bold);
		//Msgbox1(count($arrDisplay2));
		if(count($arrDisplay2)>0)
		{
			for($i=0;$i<count($arrDisplay2);$i++)
			{
				$worksheet->write($i+1, 0, $i+1);	
				//Msgbox1($arrDisplay2[$i]['status']);
				$worksheet->write($i+1, 1,$arrDisplay2[$i]['status']);
				$worksheet->write($i+1, 2, $arrDisplay2[$i]['from']);
				$worksheet->write($i+1, 3, $arrDisplay2[$i]['to']);
				$worksheet->write($i+1, 4, $arrDisplay2[$i]['frplace']);
				$worksheet->write($i+1, 5, $arrDisplay2[$i]['toplace']);
				$worksheet->write($i+1, 6, $arrDisplay2[$i]['distance']);
				$worksheet->write($i+1, 7, $arrDisplay2[$i]['tttime']);
			}
		}	
		$workbook->send('report.xls');
		$workbook->close();	

		

/////////////////////////////////////////////////////////

CloseDB();

function GetStatusString($FieldName, &$CurrVal)
{
	$arrReturn = array('Inactive', 'Active');
	$CurrVal = GetVarVal($arrReturn[$CurrVal], '---');
}
function changetime($daytime)
{
	$arrT = split(" ",$daytime);
	$arrTmp = split("/",$arrT[0]);
	$sReturn = $arrTmp[2]."-".$arrTmp[1]."-".$arrTmp[0]." ".$arrT[1];	
    return($sReturn);
}
function ConnectDB()
{
	global $dbConnect,$sDbServer,$sDbUser,$sDbPassword,$sDbName;

    $bResult = $dbConnect->NConnect($sDbServer,$sDbUser,$sDbPassword,$sDbName);
	
	
	//$dsn="PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=(local);DATABASE=sms;UID=sa;PWD=;";
	//$bResult = $dbConnect->Connect($dsn);
	
    if(!$bResult)
    {
      // neu ket noi khong thanh cong thi bao loi
      $sErr_Connect = CONNECT_ERR;
      //MsgBox($sErr_Connect);	  
      return false;
    }
    // dat che do cho phep lay index cac field theo ten
    //$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
    return true;
}


  //Ham Dong ket noi voi database.
function CloseDB()
{
    global $dbConnect;
    if(IsConnect())
    {
        $dbConnect->Close();
        return(true);
    }
    return(false);
}
function ToYMD($dt)
{
	$a = split("/", $dt);
	return $a[2]."-".$a[1]."-".$a[0];
}
function TimeToHM($number, $lang = "en")
{
	$text = "";
	$nMin = $number % 60;
	$nHour = (int)($number / 60);
	
	$text = ($nHour > 9 ? $nHour : "0$nHour").":".($nMin > 9 ? $nMin : "0$nMin");
	return $text;
}
function MsgBox1($sMessage)
{
	echo '<script language="Javascript" type="text/javascript">alert("'.$sMessage.'");</script>';    
}
?>